﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

/*
 * 
 * 在项目开发中，我们通常会遇到这样的需求：缓存中的数据需要监听数据库表，当表的数据发生变化时，更新缓存中的数据。一般情况下，我们首先想到的可能是使用 timer，但是这种方式更新数据的实时性不够。这里我们提供另外一种方式：SqlDependency 与 SQL Server Broker Servicer 组合使用，来实现当依赖的表中的数据发生变化时，在代码中做相关的操作：刷新缓存或者其他你想要的任何处理。
 * SqlDependency 非常适用于使用缓存的情况，在这种情况下您的 ASP.NET 应用程序或中间层服务需要将某些信息缓存在内存中。
 * SqlDependency 允许您在数据库中的原始数据发生更改时接收通知，以便刷新缓存。
 * 若要建立依赖项，需要将一个 SqlDependency 对象与一个或多个 SqlCommand 对象关联。要接收通知，需要订阅 OnChange 事件
 * 
 * Broker service 用于应用程序与 sql server 进行通信。当 sqlDependency 指定的依赖数据源发生变化时，通知应用程序，触发 onChange 事件
 * 
 * 会造成死循环的情况
 * ①在 where 子句中对 datetime 字段直接与字符串比较，如 where sdate='2011-9-1，此时应该用传入参数代替 where sdate=@sdate
 * ②
 * 派生表、行集函数、UNION运算符、子查询、外连接或自连接、TOP子句、DISTINCT 关键字、COUNT(*)聚合函数、AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数
 * 用户自定义的聚合函数、引用可空表达式的 SUM 函数、完全文本谓词 CONTAINS 或 FREETEXT、COMPUTE 或 COMPUTE BY 子句、INTO 子句
 * 临时表或表变量、其他数据库或服务器中的表或视图、所有其他视图或表值函数、任何系统表或视图、任何非确定性函数，包括评级和窗口函数
 * 任何服务器全局变量、任何服务中介程序队列、同义词。

 * 会触发 SqlDependency.OnChange 事件：command 指定的 commandText 的查询结果发生变化时触发，且只触发一次，故需要在触发事件中再次建立依赖和绑定接受通知
 * 建立依赖：将 SqlDependency 对象与 SqlCommand 对象绑定
 * 接收通知：Onchange事件指定
 * 
 * 可以适用的情况：
 * 简单的sql语句：select col1,col2, convert(nvarchar(10),col3,121) as sdate from dbo.tableName where cond1 and cond2 order by col1
 * 使用转换函数：select col1,col2, convert(nvarchar(10),col3,121) as sdate from dbo.tableName
 * 使用 inner join
 * 给表取别名
 * 
 * support
 * sql server 2005 + dotnet framework 2.0 upper
 */

namespace TestApp
{
    public class SqlDependencyData
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string _connectionString;

        /// <summary>
        /// SqlDependency 依赖 SQL 
        /// </summary>
        private string _dependencySql;

        /// <summary>
        /// SqlDependency 依赖 SQL 中的参数
        /// </summary>
        private IList<SqlParameter> _sqlParamList;

        /// <summary>
        /// SqlDependency 对象成员
        /// </summary>
        private SqlDependency _dependency;

        /// <summary>
        /// 对产生变化后的表做逻辑处理
        /// </summary>
        private Action<DataTable> _outputChangeShow;

        public SqlDependencyData(string connectionString, string dependencySql, IList<SqlParameter> sqlParams, Action<DataTable> outputChangeShow)
        {
            this._connectionString = connectionString;
            this._dependencySql = dependencySql;
            this._sqlParamList = sqlParams;
            this._outputChangeShow = outputChangeShow;
        }

        /// <summary>
        /// 绑定依赖，并做逻辑处理
        /// </summary>
        /// <returns></returns>
        public DataTable CheckChange( )
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(this._connectionString))
                {
                    SqlCommand cmd = new SqlCommand(this._dependencySql, conn);
                    if (this._sqlParamList != null)
                    {
                        foreach (SqlParameter p in this._sqlParamList)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    conn.Open();

                    //每次执行 CheckChange 方法都需要重新绑定数据库依赖
                    cmd.Notification = null;
                    this._dependency = new SqlDependency(cmd);
                    //this._dependency = new SqlDependency();
                    //this._dependency.AddCommandDependency(cmd);
                    this._dependency.OnChange += new OnChangeEventHandler(_dependency_OnChange);


                    SqlDataReader sdr = cmd.ExecuteReader();
                    //此处的 Parameters.Clear 必不可少，否则执行 foreach 添加 parameter 时会报错：另一个 SqlParameterCollection 中已包含 SqlParameter
                    cmd.Parameters.Clear();
                    dt.Load(sdr);
                }
                if (this._outputChangeShow != null)
                {
                    this._outputChangeShow(dt);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception:" + ex.Message);
            }
            return dt;
        }

        /// <summary>
        /// time:2011-09-03 11:50:45
        /// Notes:
        /// The OnChange event may be generated on a different thread from the thread that initiated command execution
        /// so we must execute command then we can bind onchange event.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void _dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            //该事件执行一次之后，依赖失效，故取消订阅，并调用 CheckChange 方法重新订阅依赖
            SqlDependency dep = sender as SqlDependency;
            dep.OnChange -= new OnChangeEventHandler(_dependency_OnChange);
            this.CheckChange();
        }
    }
}
